package com.scalar.chiptrack.operations.reports.actions;

import com.scalar.chiptrack.ChipTrackExtendedAction;
import com.scalar.chiptrack.commons.SessionKeys;
import com.scalar.chiptrack.commons.ExcelColumnProperties;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.operations.reports.forms.TransactionReportForm;
import com.scalar.chiptrack.operations.reports.dao.DataDumpReportsDAO;
import com.scalar.chiptrack.operations.reports.DataDumpReportsHelper;
import com.scalar.chiptrack.operations.process.dao.NewProductDAO;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionForm;
import org.apache.struts.util.LabelValueBean;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Map;
import java.util.List;


/**
 * User: Sujan
 * Date: September 11, 2008
 * Time: 12:59:18 PM
 */
public class TransactionReportAction extends ChipTrackExtendedAction {

    public ActionForward executeRequest(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception
    {
        String action = request.getParameter( "action" );
        TransactionReportForm transactionReportForm = (TransactionReportForm)form;
        HttpSession session = request.getSession();
		User user = (User) session.getAttribute( SessionKeys.USER_KEY );
		if( user == null )
		{
			session.setAttribute( SessionKeys.SOURCE_PAGE_KEY, "/transactionReportAction.do?" + request.getQueryString() );

			return( mapping.findForward( "login" ) );
		}
        Workbook workbook = null;
        String fileName = "";

        if ( action == null || "".equals(action) )
        {
            action = "transactionReportSearch";
        }
        try
        {
            if ( "transactionReportSearch".equalsIgnoreCase(action))
            {
                request.setAttribute( "monthList", DateUtil.getMonthsList() );
                request.setAttribute( "yearList", DateUtil.getYearList() );
                request.setAttribute ( "baseProductList", NewProductDAO.loadAllBaseProducts( null, true, true ));
                Calendar cal = Calendar.getInstance();
                transactionReportForm.setMonth( cal.get( Calendar.MONTH )+1);
                transactionReportForm.setYear( cal.get( Calendar.YEAR ));
                return mapping.findForward( "transactionReportSearch");
            }
            else if ( "transactionReport".equalsIgnoreCase(action)) //This CAN BE USED for ALL CUSTOMERS
            {
                request.setAttribute ( "baseProductList", NewProductDAO.loadAllBaseProducts( null, true, true ));
                request.setAttribute( "vendorList",    VendorDAO.getVendorNamesByPsType(null, "all" ));
                if ( transactionReportForm.getProcessType() != null && !"0".equalsIgnoreCase(transactionReportForm.getProcessType() ) )
                {
                    request.setAttribute( "productList", NewProductDAO.loadPartNumbers( null, 1, transactionReportForm.getProcessType(), true, true, true, transactionReportForm.getBaseProductId() ) );
                }
                else
                {
                    request.setAttribute( "productList", new ArrayList<LabelValueBean>());
                }
                request.setAttribute("processTypeList", NewProductDAO.loadAllProcessesFromDB(null, false) );

                return mapping.findForward( "transactionReport");
            }
            else if ( "generateTransactionReportXls".equalsIgnoreCase( action ) )
            {
                Map map = NewProductDAO.getTransactionReportDetails( null, transactionReportForm.getBaseProductId(), transactionReportForm.getMonth(), transactionReportForm.getYear() );
                workbook =  getTransactionWorkBook( map, transactionReportForm.getMonth(), transactionReportForm.getYear() );
                fileName = "Transaction Report_" + DateUtil.getTimeStamp() ;

                ExcelUtils.generateExcelReport( workbook, fileName, response );
            }
            else if ( "transactionReportSitimexls".equalsIgnoreCase( action ) )
            {
                ArrayList dataList = DataDumpReportsDAO.getSiTimeTransactionReports(null, transactionReportForm);

                if ( "transaction".equalsIgnoreCase(transactionReportForm.getReportType() ) )
                {
                    ExcelColumnProperties colProps1 = new ExcelColumnProperties("TO_Amount");
                    colProps1.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);

                    ExcelColumnProperties colProps2 = new ExcelColumnProperties("FROM_Amount");
                    colProps2.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
                    ExcelColumnProperties colProps5  = new ExcelColumnProperties("VENDOR");
                    ExcelColumnProperties colProps6  = new ExcelColumnProperties("Transaction_Type");
                    colProps5.setGroupBy(true);
                    colProps6.setGroupBy(true);
                     ExcelColumnProperties colProps3 = new ExcelColumnProperties("Quantity");
                     colProps3.setTotalcellStyle(ExcelColumnProperties.CELL_STYLE_NUMBER_TOTAL);
                    colProps3.setShowSubTotal(true);
                    colProps3.setShowTotal( true);
                    List<ExcelColumnProperties> list = new ArrayList<ExcelColumnProperties>();
                    list.add(colProps1);list.add(colProps2);
                    list.add(colProps3);
                    list.add(colProps5);list.add(colProps6);
                    workbook =  DataDumpReportsHelper.getWorkbook( dataList, "Transaction Report", null,null,list );
                    fileName = "Transaction Report_" + DateUtil.getTimeStamp() ;
                }
                else if ( "yield".equalsIgnoreCase(transactionReportForm.getReportType() ) )
                {
                    ExcelColumnProperties colProps1 = new ExcelColumnProperties("Std_cost");
                    colProps1.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);

                    ExcelColumnProperties colProps2 = new ExcelColumnProperties("COST_VARIANCE");
                    colProps2.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
                    colProps2.setTotalcellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$_TOTAL);
                    colProps2.setShowSubTotal(true);
                    colProps2.setShowTotal( true);
                    ExcelColumnProperties colProps3 = new ExcelColumnProperties("Actual Yield");
                    colProps3.setCellStyle(ExcelColumnProperties.CELL_STYLE_FLOAT_PER);
                    ExcelColumnProperties colProps4 = new ExcelColumnProperties("Std Yield");
                    colProps4.setCellStyle(ExcelColumnProperties.CELL_STYLE_FLOAT_PER);

                    ExcelColumnProperties colProps5  = new ExcelColumnProperties("VENDOR");
                    ExcelColumnProperties colProps6  = new ExcelColumnProperties("PROCESS");
                    colProps5.setGroupBy(true);
                    colProps6.setGroupBy(true);
                    List<ExcelColumnProperties> list = new ArrayList<ExcelColumnProperties>();
                    list.add(colProps1);list.add(colProps2);
                    list.add(colProps3);list.add(colProps4);
                    list.add(colProps5);list.add(colProps6);

                    workbook =  DataDumpReportsHelper.getWorkbook( dataList, "Transaction Yield Report", null,null,list );

                   fileName = "Transaction Yield Report_" + DateUtil.getTimeStamp() ;
                }
                else if ( "valueAdd".equalsIgnoreCase(transactionReportForm.getReportType() ) )
                {
                    ExcelColumnProperties colProps1 = new ExcelColumnProperties("Std_cost");
                    colProps1.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);

                    ExcelColumnProperties colProps2 = new ExcelColumnProperties("Total Cost");
                    colProps2.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$);
                    colProps2.setTotalcellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$_TOTAL);
                    colProps2.setShowSubTotal(true);
                    colProps2.setShowTotal( true);
                    ExcelColumnProperties colProps5  = new ExcelColumnProperties("VENDOR");
                    ExcelColumnProperties colProps6  = new ExcelColumnProperties("PROCESS");
                    colProps5.setGroupBy(true);
                    colProps6.setGroupBy(true);
                    List<ExcelColumnProperties> list = new ArrayList<ExcelColumnProperties>();
                    list.add(colProps1);
                    list.add(colProps2);
                    list.add(colProps5);list.add(colProps6);
                    workbook =  DataDumpReportsHelper.getWorkbook( dataList, "Transaction ValueAdd Report", null,null,list );
                    fileName = "Transaction ValueAdd Report_" + DateUtil.getTimeStamp() ;
                }

                ExcelUtils.generateExcelReport( workbook, fileName, response );
            }
        }
        catch ( Exception e)
        {
            //errors.put ( "Could not generate excel file", null );
            e.printStackTrace();
        }
        return null;
    }

    /**
     *
     * @param transactionDetailsMap
     * @param month starts from one
     * @param year
     * @return
     */
    private Workbook getTransactionWorkBook ( Map transactionDetailsMap, int month, int year )
    {
        String[] monthsArr = DateUtil.getMonthsArray();
        String prevMonth = (month-1)==0 ? monthsArr[11] : monthsArr[month-2];
        String currMonth = monthsArr[month-1];
        Workbook workbook    = null;
        if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = ch.createDataFormat();
        
        Sheet sheet       = workbook.createSheet( "Transaction Report " + currMonth );
        sheet.setSelected( true );

        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints ( ( short ) 14 );
        titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        titleFont.setFontName( "verdana" );
        titleFont.setColor ( IndexedColors.BLACK.getIndex());

        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints ( ( short ) 10 );
        headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        headerFont.setFontName( "verdana" );
        headerFont.setColor ( IndexedColors.BLACK.getIndex());

        Font textFont = workbook.createFont();
        textFont.setFontHeightInPoints ( ( short ) 10 );
        textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
        textFont.setFontName( "verdana" );
        textFont.setColor ( IndexedColors.BLACK.getIndex());

        CellStyle titleStyle  = workbook.createCellStyle ( );
        titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
        titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
        titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        titleStyle.setWrapText ( true );
        titleStyle.setFont ( titleFont );

        CellStyle headerStyle  = workbook.createCellStyle ( );
        headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
        headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
        headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        headerStyle.setWrapText ( true );
        headerStyle.setFont ( headerFont );

        CellStyle textStyle  = workbook.createCellStyle ( );
        textStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        textStyle.setBorderTop ( CellStyle.BORDER_THIN );
        textStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        textStyle.setBorderRight ( CellStyle.BORDER_THIN );
        textStyle.setAlignment ( CellStyle.ALIGN_LEFT );
        textStyle.setWrapText ( true );
        textStyle.setFont ( textFont );

        CellStyle textValueStyle  = workbook.createCellStyle ( );
        textValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        textValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        textValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        textValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        textValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        textValueStyle.setWrapText ( true );
        textValueStyle.setFont ( textFont );
        textValueStyle.setDataFormat( df.getFormat( "#,##0" ) );
        int nextRowNumber = 0;
        int processColIdx=0;
        int labelColIdx = 1;
        int valueColIdx = 3;
        int remarksColIdx = 5;
        sheet.setColumnWidth( processColIdx, (short) ( ( ( 14 ) * 256 ) ) );
        sheet.setColumnWidth( labelColIdx, (short) ( ( ( 30 ) * 256 ) ) );
        sheet.setColumnWidth( valueColIdx, (short) ( ( ( 18 ) * 256 ) ) );
        sheet.setColumnWidth( remarksColIdx, (short) ( ( (65) * 256 ) ) );

        Row row = sheet.createRow( nextRowNumber++ );
        Cell cell = mergeCells ( sheet, 0, 0, 0, 5, titleStyle );
        setCellValue ( cell, ch.createRichTextString("Transaction Report"), titleStyle );
        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, 0, ch.createRichTextString("Die Bank"), headerStyle  );
        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Starting Inventory"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("DB_STARTING_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("DB_STARTING_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString(prevMonth + " Month end Die Bank WIP"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Fab Shipments"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("FAB_SHIPMENTS_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("FAB_SHIPMENTS_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Fab Shipments that are received into Die Bank"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Ending Inventory"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("DB_ENDING_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("DB_ENDING_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString(currMonth + " month end Die Bank Inventory"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Total Die Moved to Assembly"), textStyle  );
        String formula = getCellReference(valueColIdx, nextRowNumber-4) + "+" + getCellReference(valueColIdx, nextRowNumber-3) + "-" + getCellReference(valueColIdx, nextRowNumber-2);
        setCellFormula( row, valueColIdx, formula, textValueStyle );
        //setCellValue( row, valueColIdx, transactionDetailsMap.get("DB_TOT_DIE_MOVED_TO_ASSEMBLY_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("DB_TOT_DIE_MOVED_TO_ASSEMBLY_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("calculated based on Starting Wip, End Wip and Receiveings"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, (short)0, ch.createRichTextString("Assembly"), headerStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Starting WIP"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("ASM_STARTING_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("ASM_STARTING_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString(prevMonth + " month end wip at Assembly"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Die Bank Shipments"), textStyle  );
        formula = getCellReference(valueColIdx, nextRowNumber-4);
        setCellFormula( row, valueColIdx, formula, textValueStyle );
        //setCellValue( row, valueColIdx, transactionDetailsMap.get("DB_TOT_DIE_MOVED_TO_ASSEMBLY_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("DB_TOT_DIE_MOVED_TO_ASSEMBLY_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString(""), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Ending WIP"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("ASM_ENDING_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("ASM_ENDING_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString(currMonth + " Month end WIP"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Actual Assembly Receiving"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("ACTUAL_ASM_RECEIVINGS") != null ? Integer.parseInt((String)transactionDetailsMap.get("ACTUAL_ASM_RECEIVINGS")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("based on the closed Shipments from Die Bank to Assembly"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ ); //empty row
        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Total Assembly Shipments"), textStyle  );
        formula = getCellReference(valueColIdx, nextRowNumber-5) + "+" + getCellReference(valueColIdx, nextRowNumber-4) + "-" + getCellReference(valueColIdx, nextRowNumber-3);
        setCellFormula( row, valueColIdx, formula, textValueStyle );
        //setCellValue( row, valueColIdx, transactionDetailsMap.get("TOT_ASM_SHIPMENTS") != null ? Integer.parseInt((String)transactionDetailsMap.get("TOT_ASM_SHIPMENTS")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("calculated based on Starting Wip, End Wip and Receiveings"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Actual Assembly Shipments"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("ACTUAL_ASM_SHIPMENTS") != null ? Integer.parseInt((String)transactionDetailsMap.get("ACTUAL_ASM_SHIPMENTS")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("based on the Actual Assembly out Transactions in the system"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, 0, ch.createRichTextString("FT"), headerStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Starting WIP"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("FT_STARTING_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("FT_STARTING_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString(prevMonth + " month end wip in FT"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Assembly Shipments"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("ASM_SHIPMENTS_TO_FT") != null ? Integer.parseInt((String)transactionDetailsMap.get("ASM_SHIPMENTS_TO_FT")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Actual Shipments from Assembly to FT"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Ending WIP"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("FT_ENDING_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("FT_ENDING_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString(currMonth + " month end WIP"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Actual Test Receiving"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("ACTUAL_FT_RECEIVINGS") != null ? Integer.parseInt((String)transactionDetailsMap.get("ACTUAL_FT_RECEIVINGS")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Actual Shipments received from FT"), textStyle  );
        
        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Total FT Shipments"), textStyle  );
        formula = getCellReference(valueColIdx, nextRowNumber-5) + "+" + getCellReference(valueColIdx, nextRowNumber-4) + "-" + getCellReference(valueColIdx, nextRowNumber-3);
        setCellFormula( row, valueColIdx, formula, textValueStyle );
        //setCellValue( row, valueColIdx, transactionDetailsMap.get("TOT_FT_SHIPMENTS") != null ? Integer.parseInt((String)transactionDetailsMap.get("TOT_FT_SHIPMENTS")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Calculated Field"), textStyle  );
        
        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Shipments to Tape & Reel"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("FT_SHIPMENTS_TO_TR") != null ? Integer.parseInt((String)transactionDetailsMap.get("FT_SHIPMENTS_TO_TR")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Shipments to Tape & Reel based on Shipment"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Shipments to Finished Goods"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("FT_SHIPMENTS_TO_FG") != null ? Integer.parseInt((String)transactionDetailsMap.get("FT_SHIPMENTS_TO_FG")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Shipments to Tape & Reel based on Shipment"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Actual FT Shipments"), textStyle  );
        formula = getCellReference(valueColIdx, nextRowNumber-2) + "+" + getCellReference(valueColIdx, nextRowNumber-1);
        setCellFormula( row, valueColIdx, formula, textValueStyle );
        //setCellValue( row, valueColIdx, transactionDetailsMap.get("ACTUAL_FT_SHIPMENTS") != null ? Integer.parseInt((String)transactionDetailsMap.get("ACTUAL_FT_SHIPMENTS")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Total of shipments to FG and Tape & Reel"), textStyle  );
        
        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, (short)0, ch.createRichTextString("Tape & Reel"), headerStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Starting WIP"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("TR_STARTING_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("TR_STARTING_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString(prevMonth + " month end WIP"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("FT Shipments"), textStyle  );
        formula = getCellReference(valueColIdx, nextRowNumber-6);
        setCellFormula( row, valueColIdx, formula, textValueStyle );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Shipments from FT to Tape & Reel"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Bulk Shipments"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("FG_SHIPMENTS_TO_TR") != null ? Integer.parseInt((String)transactionDetailsMap.get("FG_SHIPMENTS_TO_TR")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Transfer of Finished Goods from Bulk to Tape & Reel"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Ending WIP"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("TR_ENDING_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("TR_ENDING_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString(currMonth + " Month end WIP"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Total Tape & Reel Shipments"), textStyle  );
        formula = getCellReference(valueColIdx, nextRowNumber-5) + "+" + getCellReference(valueColIdx, nextRowNumber-4) + "+" + getCellReference(valueColIdx, nextRowNumber-3) + "-" + getCellReference(valueColIdx, nextRowNumber-2);
        setCellFormula( row, valueColIdx, formula, textValueStyle );
        //setCellValue( row, valueColIdx, transactionDetailsMap.get("TOT_TR_SHIPMENTS") != null ? Integer.parseInt((String)transactionDetailsMap.get("TOT_TR_SHIPMENTS")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Calculated shipments"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Actual Tape & Reel Shipments"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("ACTUAL_TR_SHIPMENTS") != null ? Integer.parseInt((String)transactionDetailsMap.get("ACTUAL_TR_SHIPMENTS")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Actual Shipments based on Transactions"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, (short)0, ch.createRichTextString("FG"), headerStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Starting Inventory"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("FG_STARTING_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("FG_STARTING_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString(prevMonth + " Month end inventory"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("FT Shipments"), textStyle  );
        formula = getCellReference(valueColIdx, nextRowNumber-14);
        setCellFormula( row, valueColIdx, formula, textValueStyle );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Receving from FT"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Tape & reel Shipments"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("TR_SHIPMENTS_TO_FG") != null ? Integer.parseInt((String)transactionDetailsMap.get("TR_SHIPMENTS_TO_FG")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Receving from Tape & Reel"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Ending Inventory"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("FG_ENDING_WIP") != null ? Integer.parseInt((String)transactionDetailsMap.get("FG_ENDING_WIP")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString(currMonth + " month end Inventory"), textStyle  );
        
        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("FG Shipments"), textStyle  );
        formula = getCellReference(valueColIdx, nextRowNumber-5) + "+" + getCellReference(valueColIdx, nextRowNumber-4) + "+" + getCellReference(valueColIdx, nextRowNumber-3) + "-" + getCellReference(valueColIdx, nextRowNumber-2);
        setCellFormula( row, valueColIdx, formula, textValueStyle );
        //setCellValue( row, valueColIdx, transactionDetailsMap.get("TOT_FG_SHIPMENTS") != null ? Integer.parseInt((String)transactionDetailsMap.get("TOT_FG_SHIPMENTS")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Calculated FG Shipments"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Shipments to Customers"), textStyle  );
        setCellValue( row, valueColIdx, transactionDetailsMap.get("SHIPMENTS_TO_CUSTOMERS") != null ? Integer.parseInt((String)transactionDetailsMap.get("SHIPMENTS_TO_CUSTOMERS")):0 , textValueStyle, Cell.CELL_TYPE_NUMERIC  );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Shipments against SO"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Transfer to Tape & Reel"), textStyle  );
        formula = getCellReference(valueColIdx, nextRowNumber-15);
        setCellFormula( row, valueColIdx, formula, textValueStyle );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Tranfer to Tape & Reel"), textStyle  );

        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        setCellValue( row, labelColIdx, ch.createRichTextString("Actual Shipments"), textStyle  );
        formula = getCellReference(valueColIdx, nextRowNumber-3) + "+" +getCellReference(valueColIdx, nextRowNumber-2);
        setCellFormula( row, valueColIdx, formula, textValueStyle );
        setCellValue( row, remarksColIdx, ch.createRichTextString("Calculated Field"), textStyle  );
        
        return workbook;
    }
    /**
     * merges cells and sets each cell with the given cellStyle.
     * @param sheet
     * sheet object in which the cells has to be merged.
     * @param startRow
     * start row Number to merge from.<br>
     * starts from 0.
     * @param startCell
     * start cell number in the start row to merge from.<br>
     * starts from 0.
     * @param endRow
     * end row Number<br>
     * starts from 0.
     * @param endCell
     * end cell number<br>
     * starts from 0.
     * @param endCell
     * @return
     * returns cell object of the startRow, startCell.
     */
    private Cell mergeCells ( Sheet sheet, int startRow, int startCell, int endRow, int endCell, CellStyle cellStyle )
    {
        sheet.addMergedRegion( new CellRangeAddress( startRow, endRow, startCell, endCell ) );
        Cell cell = null;
        for ( int rowIdx = startRow; rowIdx <= endRow; rowIdx++ )
        {
            for ( int cellIdx = startCell; cellIdx <= endCell; cellIdx++ )
            {
                cell = getCellAt ( sheet.getRow ( rowIdx ), cellIdx );
                cell.setCellStyle ( cellStyle );
            }
        }
        return getCellAt ( sheet.getRow( startRow), startCell );
    }
    /**
     * Returns the cell object at the specified cell in the row.<br>
     * It creats cell if doesn't exists.
     * @param row
     * row object from which cell object has to be picked
     * @param cellNumber
     * starts from 0.
     * @return
     */
    private Cell getCellAt ( Row row, int cellNumber )
    {
        Cell cell = null;
        cell = row.getCell( cellNumber );
        if ( cell == null )
        {
            cell = row.createCell ( cellNumber );
        }
        return cell;
    }
    private void setCellValue ( Cell cell, RichTextString value, CellStyle cellStyle )
    {
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }
    private void setCellValue ( Row row, int cellNo, RichTextString value, CellStyle cellStyle )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }
     private void setCellValue ( Row row, int cellNo, int value, CellStyle cellStyle, int cellType )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
        cell.setCellType( cellType );
    }
    private static void setCellFormula ( Row row, int cellNo, String formula, CellStyle cellStyle )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellFormula( formula );
    }
    /**
     *
     * @param idx - zero based index
     *
     * @return
     */
    private static String getAlphaforIdx ( int idx )
    {
        String[] alpha = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};
        String formatted = "";
        if ( idx < alpha.length )
        {
            formatted = alpha[idx];
        }
        else
        {
            //idx=idx+1;
            int idx1 = (idx/26)-1;
            int idx2 = (idx%26);
            if ( idx1 >= 0 )
            {
                formatted = alpha[idx1];
            }
            if ( idx2 >= 0 )
            {
                formatted = formatted+alpha[idx2];
            }
        }
        return formatted;
    }

    /**
     *
     * @param columnIdx  zero based
     * @param rowNumber starts from one
     * @return
     */
    private String getCellReference ( int columnIdx, int rowNumber )
    {
        return getAlphaforIdx( columnIdx ) + rowNumber;
    }
}
